/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import dto.Project;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import utils.SQLServerDB;

/**
 *
 * @author Canh
 */
public class ProjectDAO {

    final String PROJECT_READALL = "SELECT * FROM PROJECTS";
    final String PROJECT_READBYID = "SELECT * FROM PROJECTS WHERE ID = ?";
    final String PROJECT_CREATE = "INSERT INTO PROJECTS VALUES (?,?)";
    final String PROJECT_UPDATE = "UPDATE PROJECTS SET NAME = ?, Description = ? WHERE ID = ?";
    final String PROJECT_DELETE = "{call project_Delete(?)}";

    public boolean create(Project p) {
        try {
            SQLServerDB db = new SQLServerDB();
            PreparedStatement pr = db.getConn().prepareStatement(PROJECT_CREATE);
            pr.setString(1, p.getName());
            pr.setString(2, p.getDescript());
            ResultSet rs = pr.executeQuery();
            return true;
        } catch (SQLException ex) {
            System.out.println(ex.toString());
        }
        return false;
    }

    public ArrayList<Project> readAll() {
        ArrayList<Project> projects = new ArrayList<Project>();
        try {
            SQLServerDB db = new SQLServerDB();
            PreparedStatement pr = db.getConn().prepareStatement(PROJECT_READALL);
            ResultSet rs = pr.executeQuery();
            while (rs.next()) {
                Project p = new Project(rs.getInt(1), rs.getString(2), rs.getString(3));
                projects.add(p);
            }
        } catch (SQLException ex) {

        }
        return projects;
    }

    public Project readByID(int id) {
        try {
            SQLServerDB db = new SQLServerDB();
            PreparedStatement pr = db.getConn().prepareStatement(PROJECT_READBYID);
            pr.setInt(1, id);
            ResultSet rs = pr.executeQuery();
            if (rs.next()) {
                Project p = new Project(rs.getInt(1), rs.getString(2), rs.getString(3));
                return p;
            }
        } catch (SQLException ex) {

        }
        return null;
    }

    public boolean update(Project p) {
        try {
            SQLServerDB db = new SQLServerDB();
            PreparedStatement pr = db.getConn().prepareStatement(PROJECT_UPDATE);
            pr.setString(1, p.getName());
            pr.setString(2, p.getDescript());
            pr.setInt(3, p.getId());
            if (pr.executeUpdate() > 0) {
                return true;
            }
        } catch (SQLException ex) {
            System.out.println(ex.toString());
        }
        return false;
    }

    public boolean delete(int id) {
        try {

            SQLServerDB db = new SQLServerDB();
            CallableStatement pr = db.getConn().prepareCall(PROJECT_DELETE);
            pr.setInt(1, id);
            if (pr.executeUpdate() > 0) {
                return true;
            }
        } catch (SQLException ex) {
            System.out.println(ex.toString());
        }
        return false;
    }
}
